# !/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
@作者 杨阳
@时间 2018-06-01
@说明 自动生成代码的工具类，生成包含实体类，页面
@注意事项
"""
import os

from util.generation.generationutil import GenerationConstant
from util.generation.generationutil import GenerationMySqlUtil
from util.generation.generationutil.GenerationConstant import 不生成在修改中的列, 不生成在查询中的列, 不生成在增加中的列
from util.generation.generationutil.GenerationMySqlUtil import get_database_tables
from util.generation.generationutil.GenerationWriteFileUtil import w, tab, space, change_line


def open_model_file(full_path):
    """
    生成并打开实体类文件
    :param  full_path   文件全路径，包含文件名
    :return:    返回文件的对象
    """
    # 判断model文件是否存在，如果存在删除旧
    if os.path.exists(full_path):
        os.remove(full_path)
    # 如果文件不存在则创建后打开
    fp = open(full_path, 'w')
    return fp


def write_encoding_and_annotation(fp):
    """
    写入model类的文件编码以及注释
    :param  fp  文件对象
    :return:
    """
    w(fp, "# !/usr/bin/env python3")
    w(fp, "# -*- coding: utf-8 -*-")
    w(fp, "")
    w(fp, "\"\"\"")
    w(fp, "@作者 杨阳")
    w(fp, "@时间 2018-06-01")
    w(fp, "@说明 自动生成代码")
    w(fp, "@注意事项 需要自生行添加外键")
    w(fp, "\"\"\"")

    # 引入需要的文件
    w(fp, "from util.ThreadPluginsInitUtil import db")
    w(fp, "import json")
    w(fp, "from util.StrUtil import get_uuid")
    w(fp, "from sqlalchemy.sql.functions import current_time")
    w(fp, "from flask_login import current_user")
    w(fp, "from util.JsonUtil import classToDict, JsonEncoder")


def write_get_entity_byId(fp, model_name):
    """
    为每一个实体类生成一个通过id获取实体的方法
    :param fp:              文件操作对象
    :param model_name:      实体类的名称
    :return:
    """
    w(fp, tab() + "@staticmethod")
    w(fp, tab() + "def get_entity_byId(id):")
    w(fp, tab() + tab() + "\"\"\"")
    w(fp, tab() + tab() + "根据id获得实体类对象")
    w(fp, tab() + tab() + ":param   id  实体类id")
    w(fp, tab() + tab() + "\"\"\"")
    w(fp, tab() + tab() + "entity={}.query.filter({}.id==id).first()".format(model_name, model_name))
    w(fp, tab() + tab() + "return entity")
    w(fp, tab() + "")


def write_add_entity(fp, columns, model_name):
    """
    生成增加实体类的方法
    :param fp:              操作文件的对象
    :param columns:         表中所有列
    :param model_name:      实体类名称
    :return:
    """
    w(fp, tab() + "@staticmethod")
    column_names = []

    # 这个是为了拼方法中的参数的
    for column in columns:
        column_name = column["COLUMN_NAME"]
        if column_name not in 不生成在增加中的列:
            column_names.append(column_name)
    w(fp, tab() + "def add_entity({}):".format(','.join(column_names)))

    w(fp, tab() + tab() + "\"\"\"")
    w(fp, tab() + tab() + "增加实体类")
    for column in columns:
        column_name = column["COLUMN_NAME"]
        column_comment = column["COLUMN_COMMENT"]
        if column_name not in 不生成在增加中的列:
            w(fp, tab() + tab() + ":param   {}  {}".format(column_name, column_comment))
    w(fp, tab() + tab() + "\"\"\"")
    w(fp, tab() + tab() + "try:")
    w(fp, tab() + tab() + tab() + "entity = {}()".format(model_name))
    for column in columns:
        column_name = column["COLUMN_NAME"]
        if column_name != "id":
            if column_name == "create_time":
                w(fp, tab() + tab() + tab() + "entity.{}=db.cast(current_time(), db.DateTime)"
                  .format(column_name, column_name))
            elif column_name == "creator":
                w(fp, tab() + tab() + tab() + "entity.{}=current_user.id"
                  .format(column_name, column_name))
            else:
                w(fp, tab() + tab() + tab() + "entity.{}={}"
                  .format(column_name, column_name))
        else:
            w(fp, tab() + tab() + tab() + "entity.id=get_uuid()"
              .format(column_name, column_name))

    w(fp, tab() + tab() + tab() + "db.session.add(entity)")
    w(fp, tab() + tab() + tab() + "db.session.commit()")
    w(fp, tab() + tab() + tab() + "result = {\"success\": True, \"msg\": \"增加成功!\"}")
    w(fp, tab() + tab() + "except Exception as err:")
    w(fp, tab() + tab() + tab() + "db.session.rollback()")
    w(fp, tab() + tab() + tab() + "result = {\"success\": True, \"msg\": \"增加失败，请重试!\"}")
    w(fp, tab() + tab() + "finally:")
    w(fp, tab() + tab() + tab() + "pass")
    w(fp, tab() + tab() + "return json.dumps(result)")


def write_update_entity(fp, columns, model_name):
    """
    生成更新数据库的方法
    :param fp:              文件操作对象
    :param columns:         表中列的集合
    :param model_name:      表对应实体的名称
    :return:
    """
    w(fp, tab() + "@staticmethod")
    column_names = []

    # 这个是为了拼方法中的参数的
    for column in columns:
        column_name = column["COLUMN_NAME"]
        if column_name not in 不生成在修改中的列:
            column_names.append(column_name)
    w(fp, tab() + "def update_entity({}):".format(','.join(column_names)))

    w(fp, tab() + tab() + "\"\"\"")
    w(fp, tab() + tab() + "修改数据库")
    for column in columns:
        column_name = column["COLUMN_NAME"]
        column_comment = column["COLUMN_COMMENT"]
        if column_name not in 不生成在修改中的列:
            w(fp, tab() + tab() + ":param   {}  {}"
              .format(column_name, column_comment))
    w(fp, tab() + tab() + "\"\"\"")
    w(fp, tab() + tab() + "try:")
    w(fp, tab() + tab() + tab() + "entity={}.get_entity_byId(id)".format(model_name))
    for column in columns:
        column_name = column["COLUMN_NAME"]
        if column_name not in 不生成在修改中的列:
            w(fp, tab() + tab() + tab() + "entity.{}={}".format(column_name, column_name))
    w(fp, tab() + tab() + tab() + "db.session.commit()")
    w(fp, tab() + tab() + tab() + "result = {\"success\": True, \"msg\": \"修改成功!\"}")
    w(fp, tab() + tab() + "except Exception as err:")
    w(fp, tab() + tab() + tab() + "db.session.rollback()")
    w(fp, tab() + tab() + tab() + "result = {\"success\": True, \"msg\": \"修改失败，请重试!\"}")
    w(fp, tab() + tab() + "finally:")
    w(fp, tab() + tab() + tab() + "pass")
    w(fp, tab() + tab() + "return json.dumps(result)")


def write_entity_grid(fp, columns, model_name):
    """
    生成查询表格的方法
    :param fp:          操作文件的对象
    :param model_name:  对应数据库表的实体类名称
    :param columns:     表对就在列集合
    :return:
    """
    w(fp, tab() + "@staticmethod")
    # 这个是为了拼方法中的参数的
    column_names = []
    for column in columns:
        data_type = column["DATA_TYPE"]
        column_name = column["COLUMN_NAME"]
        if column_name not in 不生成在查询中的列:
            if data_type == "datetime" or data_type == "date":
                column_names.append("search_start_" + column_name)
                column_names.append("search_end_" + column_name)
            else:
                column_names.append("search_" + column_name)

    w(fp, tab() + "def query_entity_grid(group_id, {}, current_page=1):".format(",".join(column_names)))

    w(fp, tab() + tab() + "\"\"\"")
    w(fp, tab() + tab() + "查询表格数据")
    w(fp, tab() + tab() + "\"\"\"")

    w(fp, tab() + tab() + "filters = {1==1}")

    for column in columns:
        column_name = column["COLUMN_NAME"]
        data_type = column["DATA_TYPE"]
        if column_name not in 不生成在查询中的列:
            if data_type == 'datetime' or data_type == 'date':
                w(fp, tab() + tab() + "if search_start_{} is not None and search_start_{} != '':"
                  .format(column_name, column_name))
                w(fp, tab() + tab() + tab() + "filters.add({}.{} >= search_start_{})"
                  .format(model_name, column_name, column_name))

                w(fp, tab() + tab() + "if search_end_{} is not None and search_end_{} != '':"
                  .format(column_name, column_name))
                w(fp, tab() + tab() + tab() + "filters.add({}.{} <= search_end_{})"
                  .format(model_name, column_name, column_name))
            else:
                w(fp, tab() + tab() + "if search_{} is not None and search_{} != '':"
                  .format(column_name, column_name))
                w(fp, tab() + tab() + tab() + "filters.add({}.{} == search_{})"
                  .format(model_name, column_name, column_name))

    # 最后这里再拼一个通过机构查询的
    w(fp, tab() + tab() + "if group_id is not None and group_id != '':".format(column_name, column_name))
    w(fp, tab() + tab() + tab() + "filters.add({}.group_id==group_id)".format(model_name, column_name, column_name))

    w(fp, tab() + tab() + "# 查询数据集合")
    w(fp, tab() + tab() +
      "entity_list={}.query.filter(*filters).order_by({}.create_time.desc()).limit(10).offset((int(current_page) - 1) * 10)"
      .format(model_name, model_name))

    w(fp, tab() + tab() + "# 查询总记录数")
    w(fp, tab() + tab() + "total=db.session.query({}.id).filter(*filters).count()"
      .format(model_name))

    w(fp, tab() + tab() + "list = []")

    w(fp, tab() + tab() + "for obj in entity_list:")
    w(fp, tab() + tab() + tab() + "list.append(classToDict(obj))")

    w(fp, tab() + tab() + "retu = {\"success\": True, \"grid\": list, \"total_count\": total}")
    w(fp, tab() + tab() + "result = json.dumps(retu, cls=JsonEncoder)")
    w(fp, tab() + tab() + "return result")


def write_delete_entity_byId(fp, model_name):
    """
    为每一个实体类生成根据id删除实体类的方法
    :param fp:              文件操作对象
    :param model_name:      实体类名称
    :return:
    """
    w(fp, tab() + "@staticmethod")
    w(fp, tab() + "def delete_entity_byId(id):")
    w(fp, tab() + tab() + "\"\"\"")
    w(fp, tab() + tab() + "根据id删除数据")
    w(fp, tab() + tab() + ":param   id  实体类id")
    w(fp, tab() + tab() + "\"\"\"")
    w(fp, tab() + tab() + "try:")
    w(fp, tab() + tab() + tab() + "entity={}.get_entity_byId(id)".format(model_name))
    w(fp, tab() + tab() + tab() + "db.session.delete(entity)")
    w(fp, tab() + tab() + tab() + "db.session.commit()")
    w(fp, tab() + tab() + tab() + "result = {\"success\": True, \"msg\": \"删除成功!\"}")
    w(fp, tab() + tab() + "except Exception as err:")
    w(fp, tab() + tab() + tab() + "db.session.rollback()")
    w(fp, tab() + tab() + tab() + "result = {\"success\": True, \"msg\": \"删除失败，请重试!\"}")
    w(fp, tab() + tab() + "finally:")
    w(fp, tab() + tab() + tab() + "pass")
    w(fp, tab() + tab() + "return json.dumps(result)")


def write_model_attribute(fp, column):
    """
    生成实体类属性，即对应的表的字段
    :param  fp          文件的操作对象
    :param  column      整个列的字典
    :return:
    """
    # 列名
    column_name = column["COLUMN_NAME"]
    # 列的类型
    data_type = column["DATA_TYPE"]
    # 列的长度
    column_length = column["CHARACTER_MAXIMUM_LENGTH"]
    # 注释
    column_comment = column["COLUMN_COMMENT"]
    # 是否可以为空 NO YES
    column_nullable = column["IS_NULLABLE"]
    null_able = False
    if column_nullable == "YES":
        null_able = True

    column_type = ""
    if data_type == "char" or data_type == "varchar":
        if column_name == 'id':
            w(fp, tab() +
              "{}=db.Column(db.String({}), primary_key=True, index=True, nullable={}, comment='''{}''')".
              format(column_name, column_length, null_able, column_comment))
        else:
            w(fp, tab() +
              "{}=db.Column(db.String({}), nullable={}, comment='''{}''')"
              .format(column_name, column_length, null_able, column_comment))
    elif data_type == "int" or data_type == "bigint":
        w(fp, tab() +
          "{}=db.Column(db.Integer, nullable={}, comment='''{}''')"
          .format(column_name, null_able, column_comment))
    elif data_type == "longblob":
        w(fp, tab() +
          "{}=db.Column(db.LargeBinary, nullable={}, comment='''{}''')"
          .format(column_name, null_able, column_comment))
    elif data_type == "datetime":
        w(fp, tab() +
          "{}=db.Column(db.DateTime, nullable={}, comment='''{}''')"
          .format(column_name, null_able, column_comment))
    elif data_type == "float":
        w(fp, tab() +
          "{}=db.Column(db.Float, nullable={}, comment='''{}''')"
          .format(column_name, null_able, column_comment))
    elif data_type == "date":
        w(fp, tab() +
          "{}=db.Column(db.date, nullable={}, comment='''{}''')"
          .format(column_name, null_able, column_comment))

    print("列：{}".format(column))


def generation_model():
    """
    生成model类的方法
    :return:
    """

    tables = get_database_tables()
    for table in tables:

        table_name = table['table_name']
        model_name = table['model_name']

        # 1. 生成model文件
        fp = open_model_file(GenerationConstant.model_file_path + table["model_name"] + ".py")

        # 2. 写入文件编码及注释
        write_encoding_and_annotation(fp)
        change_line(fp)

        # 4.写入类，类注释，类对应的表名
        w(fp, "class" + space() + model_name + "(db.Model):")
        w(fp, tab() + "\"\"\"")
        w(fp, tab() + "与{}表对应的实体类".format(table_name))
        w(fp, tab() + "\"\"\"")
        w(fp, tab() + "__tablename__ = '{}'".format(table_name))

        # 5. 根据表名，查询出表中的列
        columns = GenerationMySqlUtil.get_table_columns(table_name)
        for column in columns:
            # 开始生成类属性
            write_model_attribute(fp, column)

        change_line(fp)
        # 生成get_entity_byId方法
        write_get_entity_byId(fp, model_name)
        change_line(fp)

        # 生成delete_entity_byId方法
        write_delete_entity_byId(fp, model_name)
        change_line(fp)

        # 生成add_entity方法
        write_add_entity(fp, columns, model_name)
        change_line(fp)

        # 生成update_entity方法
        write_update_entity(fp, columns, model_name)
        change_line(fp)

        # 生成entity_grid查询表格的方法
        write_entity_grid(fp, columns, model_name)
        change_line(fp)

        change_line(fp)
    fp.close()


if __name__ == '__main__':
    generation_model()
